﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace Client
{
    class DBHandle
    {
        public string URL = "Data Source=192.168.1.130;Initial Catalog=RedRomanticKTV;User ID=sa;Password=123456@qwe";

        public int GetCount(int choes)//取得数量
        {
            string sql2 = "";
            if (choes==0)
            {
                sql2 = string.Format("select count(*) from SingerInfo;");
            }
            else if (choes==1)
            {
                sql2 = string.Format(" select count(*) from SingerInfo where SingerType=1;");
            }
            else if (choes == 2)
            {
                sql2 = string.Format(" select count(*) from SingerInfo where SingerType=2;");
            }
            else if (choes == 3)
            {
                sql2 = string.Format(" select count(*) from SingerInfo where SingerType=3;");
            }
            else if (choes == 4)
            {
                sql2 = string.Format(" select count(*) from SingerInfo where SingerType=4;");
            }
            else if (choes == 5)
            {
                sql2 = string.Format(" select count(*) from SingerInfo where SingerType=5;");
            }
            else if (choes==6)
            {
                sql2 = string.Format(@"select count(*) from SongsInfo;");
            }
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand2 = new SqlCommand(sql2, sqlConnection);
                int count = Convert.ToInt32(sqlCommand2.ExecuteScalar());
                return count;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public int[] GetSongsID(int choes,int getCount)//取得歌手id
        {
            int count = GetCount(getCount);            
            string sql = "";
            if (choes==0)
            {
                sql= string.Format(" select[id], [SingerImg] from SingerInfo");
            }
            else if (choes==1)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=1;");
            }
            else if (choes == 2)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=2;");
            }
            else if (choes == 3)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=4;");
            }
            else if (choes == 4)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=4;");
            }
            else if (choes == 5)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=5;");
            }
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);                
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                int i = 0;
                int[] id = new int[count]; ;//在本地用数组存放img的id
                while (sqlDataReader.Read())
                {
                    id[i] = Convert.ToInt32(sqlDataReader["id"]);
                    i++;
                }
                return id;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlConnection.Close();
            }

        }

        public string[] GetImgURL(int choes,int getCount)//取得图片地址
        {
            string sql = "";
            if (choes==0)
            {
                sql= string.Format(" select[id], [SingerImg] from SingerInfo");
            }
            else if (choes==1)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=1;");
            }
            else if (choes == 2)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=2;");
            }
            else if (choes == 3)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=3;");
            }
            else if (choes == 4)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=4;");
            }
            else if (choes == 5)
            {
                sql = string.Format(" select[id], [SingerImg] from SingerInfo where SingerType=5;");
            }
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

                int count = GetCount(getCount);
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                int i = 0;
                string[] url = new string[count];//在本地用数组存放img的路径
                while (sqlDataReader.Read())
                {                    
                    url[i] = Convert.ToString(sqlDataReader["SingerImg"]);
                    i++;
                }
                return url;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlConnection.Close();
            }
            

        }

        public int[] GetSongsID(int id)//取得歌曲id
        {
            int[] mp3ID = new int[10];
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql1 = string.Format(@"select  TOP (10)  SongsInfo.id,[SongsInfo].[SongsName], SongsInfo.[FileName] from SongsInfo,SingerInfo
                                              where SingerInfo.id=SongsInfo.Singer
                                              and SingerInfo.id={0}
                                              order by playNum
                                              desc;", id);
                SqlCommand sqlCommand = new SqlCommand(sql1, sqlConnection);
                SqlDataReader reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    int i = 0;
                    mp3ID[i] = Convert.ToInt32(reader["id"]);
                }
                return mp3ID;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlConnection.Close();
            }
        }
        
        public string[] GetSongsURl(int id)//取得歌曲地址
        {            
            string[] mp3Url;
            SqlConnection sqlConnection = new SqlConnection(URL);
            string sql1 ="";
            try
            {

                sqlConnection.Open();
                if (id==-1)
                {
                    mp3Url = new string[99];
                    sql1 = string.Format(@" SELECT [SongsName] ,[FileName],SingerInfo.[Name]  FROM [RedRomanticKTV].[dbo].[SongsInfo],SingerInfo
                                            where SongsInfo.Singer=SingerInfo.id;");
                }
                else
                {
                    mp3Url = new string[10];
                    sql1 = string.Format(@"select  TOP (10)  SongsInfo.id,[SongsInfo].[SongsName], SongsInfo.[FileName] from SongsInfo,SingerInfo
                                              where SingerInfo.id=SongsInfo.Singer
                                              and SingerInfo.id={0}
                                              order by playNum
                                              desc;", id);
                }
                SqlCommand sqlCommand = new SqlCommand(sql1,sqlConnection);
                SqlDataReader reader=sqlCommand.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {                       
                    mp3Url[i] = Convert.ToString(reader["FileName"]);
                    i++;
                }
                return mp3Url;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlConnection.Close();
            }
        }
    
        public string[] GetSongsName(int id)//取得歌曲名
        {
            string[] mp3Name;
            string sql1 = "";
            mp3Name = new string[99];
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                
                if (id==-1)
                {
                    sql1 = string.Format(@"SELECT [SongsName] ,[FileName],SingerInfo.[Name]  FROM [RedRomanticKTV].[dbo].[SongsInfo],SingerInfo
                                             where SongsInfo.Singer=SingerInfo.id;");
                }
                else
                {
                    sql1 = string.Format(@"select TOP (10) SongsInfo.id,[SongsInfo].[SongsName], SongsInfo.[FileName] from SongsInfo,SingerInfo
                                              where SingerInfo.id=SongsInfo.Singer
                                              and SingerInfo.id={0}
                                              order by playNum
                                              desc;", id);
                }
                SqlCommand sqlCommand = new SqlCommand(sql1, sqlConnection);
                SqlDataReader reader = sqlCommand.ExecuteReader();
                int i = 0;                                       
                while (reader.Read())
                {                    
                    mp3Name[i] = Convert.ToString(reader["SongsName"]);
                    i++;
                }
                return mp3Name;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlConnection.Close();
            }

        }
        
        public string GetSingerName(int id)
        {
            
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql = string.Format(@"select SingerInfo.[Name],SongsInfo.SongsName,SongsInfo.[FileName] from SingerInfo,SongsInfo
                                              where SongsInfo.Singer =SingerInfo.id
                                              and SingerInfo.id={0};", id);
                SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection);
                string singerName = Convert.ToString(sqlCommand.ExecuteScalar());
                return singerName;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        public bool AddRequest(string request)
        {
            SqlConnection sqlConnection = new SqlConnection(URL);
            try
            {
                sqlConnection.Open();
                string sql = string.Format("insert into UserRequest values(N'{0}',GETDATE(),0);", request);
                SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection);
                int result=sqlCommand.ExecuteNonQuery();
                if (result>0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                return false;                
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        public Songs songs(string songsname, string spell,int choes)
        {
            Songs songsInfo = new Songs();
            SqlConnection sqlConnection = new SqlConnection(URL);
            string sql ="";
            if (choes == -1)
            {
                sql = string.Format(@" SELECT [SongsName] ,[FileName],SingerInfo.[Name]  FROM [RedRomanticKTV].[dbo].[SongsInfo],SingerInfo
                                            where SongsInfo.Singer = SingerInfo.id; ");
            }
            else
            {
                sql = string.Format(@"SELECT [SongsInfo].[SongsName],[SongsInfo].[FileName],[SingerInfo].[Name] FROM [SongsInfo],[SingerInfo]
                                          WHERE ([SongsInfo].[SongsName] LIKE N'%{0}%' or [SongsInfo].[Spell] LIKE '%{1}%')
                                          and [SongsInfo].[Singer]= [SingerInfo].[id];", songsname, spell);
            }
            

            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection);
                SqlDataReader reader=sqlCommand.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    songsInfo.songsName[i] = Convert.ToString(reader["SongsName"]);
                    songsInfo.singerName[i] = Convert.ToString(reader["Name"]);
                    songsInfo.songsFileName[i] = Convert.ToString(reader["FileName"]);
                    i++;
                }
                return songsInfo;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                sqlConnection.Close();
            }
            

        }
        public SqlDataAdapter GetSingerName(string songsname,string spell)
        {
            
            SqlConnection sqlConnection = new SqlConnection(URL);
            string sql = string.Format(@"SELECT [SongsInfo].[SongsName],[SongsInfo].[FileName],[SingerInfo].[Name] FROM [SongsInfo],[SingerInfo]
                                          WHERE ([SongsInfo].[SongsName] LIKE N'%{0}%' or [SongsInfo].[Spell] LIKE '%{1}%')
                                          and [SongsInfo].[Singer]= [SingerInfo].[id];", songsname,spell);
            try
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql,sqlConnection);
                return sqlDataAdapter;
            }
            catch (Exception)
            {
                throw;
            }
            
        }      
          
    }
    public class Songs
    {
        public string[] songsName=new string[99];
        public string[] singerName = new string[99];
        public string[] songsFileName = new string[99];
        
    }
}
